﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.Data;

namespace MonthlyBudget
{
    class SqlManager
    {
        private static SQLiteConnection connection;
        private static SqlManager manager;
       private SqlManager(){}

        public static SqlManager Manager{
            get
            {
                if (manager == null)
                {
                    manager = new SqlManager();
                    connection = new SQLiteConnection("Data Source=Budget.s3db;Version=3;New=True;");
                    connection.Open();
                    string cmdstring = @"CREATE TABLE IF NOT EXISTS user(
                    username TEXT NOT NULL,
                    password TEXT NOT NULL,
                    PRIMARY KEY (username));";
                    SQLiteCommand query = new SQLiteCommand(cmdstring, connection);
                    query.ExecuteNonQuery();
                    connection.Close();
                }
                return manager;
            }
        }

        public void addUser(string username, string password)
        {    
          Execute("INSERT INTO user (username,password) VALUES ('"+username+"', '"+password+"');");           
        }
        public List<string> getUsers()
        {
            connection.Open();
            List<string> users = new List<string>();
            SQLiteDataAdapter DB;
            DataSet DS = new DataSet();
            DataTable dt = new DataTable();
            DB = new SQLiteDataAdapter("SELECT * FROM user", connection);
            DS.Reset();
            DB.Fill(DS);
            dt = DS.Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                users.Add(dr["username"].ToString());
            }
            connection.Close();
            return users;
        }
        private void Execute(string cmdstring)
        {
            connection.Open();
            SQLiteCommand query = new SQLiteCommand(cmdstring, connection);
            query.ExecuteNonQuery();
            connection.Close();
        }  
    }
}
